const express = require('express');
const { promisePool } = require('../config/database');
const { submitComplaintSchema } = require('../schema/complaints');

const router = express.Router();

// 生成投诉编号
function generateComplaintNo() {
  const now = new Date();
  const year = now.getFullYear();
  const month = String(now.getMonth() + 1).padStart(2, '0');
  const day = String(now.getDate()).padStart(2, '0');
  const timestamp = now.getTime().toString().slice(-6);
  return `C${year}${month}${day}${timestamp}`;
}

// 提交投诉建议接口
router.post('/submit', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = submitComplaintSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { type, title, content, contact, urgent } = value;
    
    // 从请求头或token中获取用户ID（这里假设从请求头获取）
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];
    const complaintNo = generateComplaintNo();
    const now = new Date();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 插入投诉建议记录
      const [result] = await connection.execute(
        `INSERT INTO complaints (
          complaint_no, user_id, type, title, content, contact, 
          status, urgent, submit_time, last_update_time
        ) VALUES (?, ?, ?, ?, ?, ?, 'pending', ?, ?, ?)`,
        [complaintNo, userId, type, title, content, contact || null, urgent, now, now]
      );

      const complaintId = result.insertId;

      // 插入状态历史记录
      await connection.execute(
        `INSERT INTO complaint_status_history (
          complaint_id, from_status, to_status, action, 
          operator_id, operator_name, operation_time, operation_comment
        ) VALUES (?, NULL, 'pending', '用户提交投诉建议', ?, ?, ?, ?)`,
        [complaintId, userId, user.name, now, `用户${user.name}提交了${type}类型的投诉建议`]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '投诉建议提交成功',
        data: {
          id: complaintId,
          complaintNo: complaintNo,
          status: 'pending',
          submitTime: now.toISOString()
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    console.error('提交投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取当前用户的投诉建议列表
router.get('/user-complaints', async (req, res) => {
  try {
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 获取查询参数
    const { status = 'all', page = 1, limit = 20 } = req.query;
    const pageNum = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 20;
    const offset = (pageNum - 1) * limitNum;

    // 构建查询条件
    let whereClause = 'WHERE c.user_id = ? AND c.deleted_at IS NULL';
    let queryParams = [userId];

    if (status !== 'all') {
      whereClause += ' AND c.status = ?';
      queryParams.push(status);
    }

    // 查询投诉建议数据
    const [complaints] = await promisePool.execute(
      `SELECT 
        c.id,
        c.complaint_no,
        c.type,
        c.title,
        c.content,
        c.contact,
        c.status,
        c.urgent,
        c.submit_time,
        c.last_update_time,
        c.processor_name,
        c.process_comment,
        c.process_time
      FROM complaints c
      ${whereClause}
      ORDER BY c.submit_time DESC
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total FROM complaints c ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;

    // 格式化返回数据
    const formattedComplaints = complaints.map(complaint => ({
      id: complaint.id,
      complaintNo: complaint.complaint_no,
      type: complaint.type,
      title: complaint.title,
      content: complaint.content,
      contact: complaint.contact,
      status: complaint.status,
      urgent: Boolean(complaint.urgent),
      submitTime: complaint.submit_time,
      lastUpdateTime: complaint.last_update_time,
      processorName: complaint.processor_name,
      processComment: complaint.process_comment,
      processTime: complaint.process_time
    }));

    res.json({
      success: true,
      message: '获取投诉建议列表成功',
      data: {
        complaints: formattedComplaints,
        pagination: {
          current: pageNum,
          pageSize: limitNum,
          total: total,
          pages: Math.ceil(total / limitNum)
        }
      }
    });

  } catch (error) {
    console.error('获取用户投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取投诉建议统计数据
router.get('/stats', async (req, res) => {
  try {
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 查询统计数据
    const [stats] = await promisePool.execute(
      `SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled
      FROM complaints 
      WHERE user_id = ? AND deleted_at IS NULL`,
      [userId]
    );

    res.json({
      success: true,
      message: '获取统计数据成功',
      data: {
        total: stats[0].total || 0,
        pending: stats[0].pending || 0,
        processing: stats[0].processing || 0,
        completed: stats[0].completed || 0,
        cancelled: stats[0].cancelled || 0
      }
    });

  } catch (error) {
    console.error('获取统计数据失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 更新投诉建议
router.put('/:id', async (req, res) => {
  try {
    const complaintId = req.params.id;
    const { type, title, content, contact, urgent } = req.body;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证投诉建议是否存在且属于当前用户
    const [complaints] = await promisePool.execute(
      'SELECT id, status FROM complaints WHERE id = ? AND user_id = ? AND deleted_at IS NULL',
      [complaintId, userId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];
    
    // 只有待处理状态的投诉可以编辑
    if (complaint.status !== 'pending') {
      return res.status(400).json({
        success: false,
        message: '只有待处理状态的投诉建议可以编辑'
      });
    }

    const now = new Date();

    // 更新投诉建议
    await promisePool.execute(
      `UPDATE complaints SET 
        type = ?, title = ?, content = ?, contact = ?, urgent = ?, 
        last_update_time = ?
      WHERE id = ?`,
      [type, title, content, contact || null, urgent, now, complaintId]
    );

    // 插入状态历史记录
    await promisePool.execute(
      `INSERT INTO complaint_status_history (
        complaint_id, from_status, to_status, action, 
        operator_id, operator_name, operation_time, operation_comment
      ) VALUES (?, 'pending', 'pending', '用户修改投诉建议', ?, ?, ?, ?)`,
      [complaintId, userId, '用户', now, '用户修改了投诉建议内容']
    );

    res.json({
      success: true,
      message: '投诉建议更新成功'
    });

  } catch (error) {
    console.error('更新投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 撤销投诉建议
router.post('/:id/cancel', async (req, res) => {
  try {
    const complaintId = req.params.id;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证投诉建议是否存在且属于当前用户
    const [complaints] = await promisePool.execute(
      'SELECT id, status FROM complaints WHERE id = ? AND user_id = ? AND deleted_at IS NULL',
      [complaintId, userId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];
    
    // 只有待处理和处理中状态的投诉可以撤销
    if (complaint.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: '已处理的投诉建议无法撤销'
      });
    }

    const now = new Date();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新投诉状态为已撤销
      await connection.execute(
        'UPDATE complaints SET status = ?, last_update_time = ? WHERE id = ?',
        ['cancelled', now, complaintId]
      );

      // 插入状态历史记录
      await connection.execute(
        `INSERT INTO complaint_status_history (
          complaint_id, from_status, to_status, action, 
          operator_id, operator_name, operation_time, operation_comment
        ) VALUES (?, ?, 'cancelled', '用户撤销投诉建议', ?, ?, ?, ?)`,
        [complaintId, complaint.status, userId, '用户', now, '用户撤销了投诉建议']
      );

      // 提交事务
      await connection.commit();

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

    res.json({
      success: true,
      message: '投诉建议撤销成功'
    });

  } catch (error) {
    console.error('撤销投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取所有村民的投诉建议列表（管理员接口）
router.get('/admin/all-complaints', async (req, res) => {
  try {
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    // 获取查询参数
    const { 
      status = 'all', 
      type = 'all', 
      urgent = 'all',
      page = 1, 
      limit = 20,
      keyword = '',
      startDate = '',
      endDate = ''
    } = req.query;
    
    const pageNum = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 20;
    const offset = (pageNum - 1) * limitNum;

    // 构建查询条件
    let whereClause = 'WHERE c.deleted_at IS NULL AND c.status != "cancelled" AND u.role = "citizen"';
    let queryParams = [];

    // 状态筛选
    if (status !== 'all') {
      whereClause += ' AND c.status = ?';
      queryParams.push(status);
    }

    // 类型筛选
    if (type !== 'all') {
      whereClause += ' AND c.type = ?';
      queryParams.push(type);
    }

    // 紧急程度筛选
    if (urgent !== 'all') {
      whereClause += ' AND c.urgent = ?';
      queryParams.push(urgent === 'true' ? 1 : 0);
    }

    // 关键词搜索
    if (keyword) {
      whereClause += ' AND (c.title LIKE ? OR c.content LIKE ? OR u.name LIKE ?)';
      const searchKeyword = `%${keyword}%`;
      queryParams.push(searchKeyword, searchKeyword, searchKeyword);
    }

    // 时间范围筛选
    if (startDate) {
      whereClause += ' AND DATE(c.submit_time) >= ?';
      queryParams.push(startDate);
    }
    if (endDate) {
      whereClause += ' AND DATE(c.submit_time) <= ?';
      queryParams.push(endDate);
    }

    // 查询投诉建议数据
    const [complaints] = await promisePool.execute(
      `SELECT 
        c.id,
        c.complaint_no,
        c.type,
        c.title,
        c.content,
        c.contact,
        c.status,
        c.urgent,
        c.submit_time,
        c.last_update_time,
        c.processor_name,
        c.process_comment,
        c.process_time,
        u.name as user_name,
        u.phone as user_phone,
        u.village as user_village
      FROM complaints c
      LEFT JOIN users u ON c.user_id = u.id
      ${whereClause}
      ORDER BY c.urgent DESC, c.submit_time DESC
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total 
       FROM complaints c
       LEFT JOIN users u ON c.user_id = u.id
       ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;

    // 格式化返回数据
    const formattedComplaints = complaints.map(complaint => ({
      id: complaint.id,
      complaintNo: complaint.complaint_no,
      type: complaint.type,
      title: complaint.title,
      content: complaint.content,
      contact: complaint.contact,
      status: complaint.status,
      urgent: Boolean(complaint.urgent),
      submitTime: complaint.submit_time,
      lastUpdateTime: complaint.last_update_time,
      processorName: complaint.processor_name,
      processComment: complaint.process_comment,
      processTime: complaint.process_time,
      userName: complaint.user_name,
      userPhone: complaint.user_phone,
      userVillage: complaint.user_village
    }));

    res.json({
      success: true,
      message: '获取投诉建议列表成功',
      data: {
        complaints: formattedComplaints,
        pagination: {
          current: pageNum,
          pageSize: limitNum,
          total: total,
          pages: Math.ceil(total / limitNum)
        }
      }
    });

  } catch (error) {
    console.error('获取投诉建议列表失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取投诉建议统计数据（管理员接口）
router.get('/admin/stats', async (req, res) => {
  try {
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    // 查询统计数据
    const [stats] = await promisePool.execute(
      `SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN c.status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN c.status = 'processing' THEN 1 ELSE 0 END) as processing,
        SUM(CASE WHEN c.status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN c.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
        SUM(CASE WHEN c.urgent = 1 THEN 1 ELSE 0 END) as urgent,
        SUM(CASE WHEN c.type = 'service' THEN 1 ELSE 0 END) as service,
        SUM(CASE WHEN c.type = 'efficiency' THEN 1 ELSE 0 END) as efficiency,
        SUM(CASE WHEN c.type = 'facility' THEN 1 ELSE 0 END) as facility,
        SUM(CASE WHEN c.type = 'other' THEN 1 ELSE 0 END) as other,
        SUM(CASE WHEN DATE(c.submit_time) = CURDATE() THEN 1 ELSE 0 END) as today,
        SUM(CASE WHEN DATE_FORMAT(c.submit_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') THEN 1 ELSE 0 END) as this_month,
        SUM(CASE WHEN YEAR(c.submit_time) = YEAR(NOW()) THEN 1 ELSE 0 END) as this_year
      FROM complaints c
      LEFT JOIN users u ON c.user_id = u.id
      WHERE c.deleted_at IS NULL AND c.status != 'cancelled' AND u.role = 'citizen'`
    );

    res.json({
      success: true,
      message: '获取统计数据成功',
      data: {
        total: stats[0].total || 0,
        pending: stats[0].pending || 0,
        processing: stats[0].processing || 0,
        completed: stats[0].completed || 0,
        cancelled: stats[0].cancelled || 0,
        urgent: stats[0].urgent || 0,
        byType: {
          service: stats[0].service || 0,
          efficiency: stats[0].efficiency || 0,
          facility: stats[0].facility || 0,
          other: stats[0].other || 0
        },
        byTime: {
          today: stats[0].today || 0,
          thisMonth: stats[0].this_month || 0,
          thisYear: stats[0].this_year || 0
        }
      }
    });

  } catch (error) {
    console.error('获取统计数据失败:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取投诉建议详情（管理员接口）
router.get('/admin/:id', async (req, res) => {
  try {
    const complaintId = req.params.id;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    // 查询投诉建议详情
    const [complaints] = await promisePool.execute(
      `SELECT 
        c.id,
        c.complaint_no,
        c.type,
        c.title,
        c.content,
        c.contact,
        c.status,
        c.urgent,
        c.submit_time,
        c.last_update_time,
        c.processor_name,
        c.process_comment,
        c.process_time,
        u.name as user_name,
        u.phone as user_phone,
        u.email as user_email,
        u.village as user_village,
        u.address as user_address
      FROM complaints c
      LEFT JOIN users u ON c.user_id = u.id
      WHERE c.id = ? AND c.deleted_at IS NULL AND u.role = 'citizen'`,
      [complaintId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];

    // 查询回复记录
    const [replies] = await promisePool.execute(
      `SELECT 
        id,
        reply_content,
        reply_by,
        reply_time,
        reply_status
      FROM complaint_replies
      WHERE complaint_id = ?
      ORDER BY reply_time ASC`,
      [complaintId]
    );

    // 查询状态历史
    const [statusHistory] = await promisePool.execute(
      `SELECT 
        from_status,
        to_status,
        action,
        operator_name,
        operation_time,
        operation_comment
      FROM complaint_status_history
      WHERE complaint_id = ?
      ORDER BY operation_time ASC`,
      [complaintId]
    );

    // 格式化返回数据
    const formattedComplaint = {
      id: complaint.id,
      complaintNo: complaint.complaint_no,
      type: complaint.type,
      title: complaint.title,
      content: complaint.content,
      contact: complaint.contact,
      status: complaint.status,
      urgent: Boolean(complaint.urgent),
      submitTime: complaint.submit_time,
      lastUpdateTime: complaint.last_update_time,
      processorName: complaint.processor_name,
      processComment: complaint.process_comment,
      processTime: complaint.process_time,
      user: {
        name: complaint.user_name,
        phone: complaint.user_phone,
        email: complaint.user_email,
        village: complaint.user_village,
        address: complaint.user_address
      },
      replies: replies.map(reply => ({
        id: reply.id,
        content: reply.reply_content,
        replyBy: reply.reply_by,
        replyTime: reply.reply_time,
        status: reply.reply_status
      })),
      statusHistory: statusHistory.map(history => ({
        fromStatus: history.from_status,
        toStatus: history.to_status,
        action: history.action,
        operatorName: history.operator_name,
        operationTime: history.operation_time,
        operationComment: history.operation_comment
      }))
    };

    res.json({
      success: true,
      message: '获取投诉建议详情成功',
      data: formattedComplaint
    });

  } catch (error) {
    console.error('获取投诉建议详情失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 回复投诉建议接口（管理员接口）
router.post('/admin/:id/reply', async (req, res) => {
  try {
    const complaintId = req.params.id;
    const { replyContent, replyStatus } = req.body;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, name, username, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    const adminUser = users[0];

    // 验证回复内容
    if (!replyContent || replyContent.trim().length === 0) {
      return res.status(400).json({
        success: false,
        message: '回复内容不能为空'
      });
    }

    if (replyContent.length > 2000) {
      return res.status(400).json({
        success: false,
        message: '回复内容不能超过2000个字符'
      });
    }

    // 验证回复状态
    if (!replyStatus || !['processing', 'completed'].includes(replyStatus)) {
      return res.status(400).json({
        success: false,
        message: '回复状态必须是处理中或已完成'
      });
    }

    // 验证投诉建议是否存在
    const [complaints] = await promisePool.execute(
      'SELECT id, status, user_id FROM complaints WHERE id = ? AND deleted_at IS NULL',
      [complaintId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];

    // 检查投诉状态，只有待处理和处理中的投诉可以回复
    if (complaint.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: '已完成的投诉建议无法回复'
      });
    }

    if (complaint.status === 'cancelled') {
      return res.status(400).json({
        success: false,
        message: '已撤销的投诉建议无法回复'
      });
    }

    const now = new Date();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新投诉建议状态和处理信息
      // 确保 processor_name 不为空
      const processorName = adminUser.name || adminUser.username || '管理员'
      
      await connection.execute(
        `UPDATE complaints SET 
          status = ?, 
          processor_id = ?, 
          processor_name = ?, 
          process_comment = ?, 
          process_time = ?, 
          last_update_time = ?
        WHERE id = ?`,
        [
          replyStatus, 
          adminUser.id, 
          processorName, 
          replyContent, 
          now, 
          now, 
          complaintId
        ]
      );

      // 插入回复记录
      // 确保 reply_by 不为空，如果用户名为空则使用默认值
      const replyByName = adminUser.name || adminUser.username || '管理员'
      
      await connection.execute(
        `INSERT INTO complaint_replies (
          complaint_id, reply_content, reply_by, reply_by_id, 
          reply_time, reply_status
        ) VALUES (?, ?, ?, ?, ?, ?)`,
        [
          complaintId, 
          replyContent, 
          replyByName, 
          adminUser.id, 
          now, 
          replyStatus
        ]
      );

      // 插入状态历史记录
      await connection.execute(
        `INSERT INTO complaint_status_history (
          complaint_id, from_status, to_status, action, 
          operator_id, operator_name, operation_time, operation_comment
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          complaintId, 
          complaint.status, 
          replyStatus, 
          '管理员回复', 
          adminUser.id, 
          processorName, 
          now, 
          `管理员${processorName}回复了投诉建议，状态变更为${replyStatus === 'processing' ? '处理中' : '已完成'}`
        ]
      );

      // 提交事务
      await connection.commit();

      // 查询完整的回复数据返回给前端
      const [replyData] = await promisePool.execute(
        `SELECT 
          cr.id as reply_id,
          cr.complaint_id,
          cr.reply_content,
          cr.reply_by,
          cr.reply_by_id,
          cr.reply_time,
          cr.reply_status,
          cr.created_at,
          c.status as complaint_status,
          c.processor_name,
          c.process_time
        FROM complaint_replies cr
        LEFT JOIN complaints c ON cr.complaint_id = c.id
        WHERE cr.complaint_id = ? 
        ORDER BY cr.reply_time DESC
        LIMIT 1`,
        [complaintId]
      );

      const replyInfo = replyData[0];

      // 构建回复对象
      const reply = {
        id: replyInfo.reply_id,
        complaintId: complaintId,
        content: replyInfo.reply_content,
        replyBy: replyInfo.reply_by,
        replyById: replyInfo.reply_by_id,
        replyTime: replyInfo.reply_time,
        status: replyInfo.reply_status,
        createdAt: replyInfo.created_at,
        // 格式化时间显示
        replyTimeFormatted: new Date(replyInfo.reply_time).toLocaleString('zh-CN')
      };

      // 构建投诉状态更新对象
      const complaintUpdate = {
        status: replyInfo.complaint_status,
        processorName: replyInfo.processor_name,
        processTime: replyInfo.process_time,
        processTimeFormatted: replyInfo.process_time ? new Date(replyInfo.process_time).toLocaleString('zh-CN') : null
      };

      res.json({
        success: true,
        message: '回复提交成功',
        reply: reply,
        complaintUpdate: complaintUpdate
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    console.error('回复投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 标记投诉建议为已完成（管理员接口）
router.post('/admin/:id/complete', async (req, res) => {
  try {
    const complaintId = req.params.id;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, name, username, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    const adminUser = users[0];

    // 验证投诉建议是否存在
    const [complaints] = await promisePool.execute(
      'SELECT id, status, user_id, title FROM complaints WHERE id = ? AND deleted_at IS NULL',
      [complaintId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];

    // 检查投诉状态，只有处理中的投诉可以标记为已完成
    if (complaint.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: '该投诉建议已经是已完成状态'
      });
    }

    if (complaint.status === 'pending') {
      return res.status(400).json({
        success: false,
        message: '待处理状态的投诉建议需要先开始处理'
      });
    }

    if (complaint.status === 'cancelled') {
      return res.status(400).json({
        success: false,
        message: '已撤销的投诉建议无法标记为已完成'
      });
    }

    const now = new Date();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新投诉建议状态为已完成
      await connection.execute(
        `UPDATE complaints SET 
          status = 'completed',
          processor_id = ?,
          processor_name = ?,
          process_comment = '管理员标记为已完成',
          process_time = ?,
          last_update_time = ?
        WHERE id = ?`,
        [
          adminUser.id,
          adminUser.name || adminUser.username || '管理员',
          now,
          now,
          complaintId
        ]
      );

      // 插入状态历史记录
      await connection.execute(
        `INSERT INTO complaint_status_history (
          complaint_id, from_status, to_status, action, 
          operator_id, operator_name, operation_time, operation_comment
        ) VALUES (?, ?, 'completed', '管理员标记完成', ?, ?, ?, ?)`,
        [
          complaintId, 
          complaint.status, 
          adminUser.id, 
          adminUser.name || adminUser.username || '管理员', 
          now, 
          `管理员${adminUser.name || adminUser.username || '管理员'}将投诉建议标记为已完成`
        ]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '投诉建议已标记为已完成',
        data: {
          complaintId: complaintId,
          title: complaint.title,
          newStatus: 'completed',
          completeTime: now.toISOString(),
          operator: adminUser.name || adminUser.username || '管理员'
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    console.error('标记投诉建议完成失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 重新处理投诉建议接口（管理员接口）
router.post('/admin/:id/reprocess', async (req, res) => {
  try {
    const complaintId = req.params.id;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.body.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否为管理员
    const [users] = await promisePool.execute(
      'SELECT id, name, username, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    if (users[0].role !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    const adminUser = users[0];

    // 验证投诉建议是否存在
    const [complaints] = await promisePool.execute(
      'SELECT id, status, user_id FROM complaints WHERE id = ? AND deleted_at IS NULL',
      [complaintId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];

    // 检查投诉状态，只有处理中和已完成的投诉可以重新处理
    if (complaint.status === 'pending') {
      return res.status(400).json({
        success: false,
        message: '待处理状态的投诉建议无需重新处理'
      });
    }

    if (complaint.status === 'cancelled') {
      return res.status(400).json({
        success: false,
        message: '已撤销的投诉建议无法重新处理'
      });
    }

    const now = new Date();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 1. 删除所有相关的回复记录
      await connection.execute(
        'DELETE FROM complaint_replies WHERE complaint_id = ?',
        [complaintId]
      );

      // 2. 删除所有相关的状态历史记录
      await connection.execute(
        'DELETE FROM complaint_status_history WHERE complaint_id = ?',
        [complaintId]
      );

      // 3. 重置投诉建议状态为待处理，清空处理人信息
      await connection.execute(
        `UPDATE complaints SET 
          status = 'pending',
          processor_id = NULL,
          processor_name = NULL,
          process_comment = NULL,
          process_time = NULL,
          last_update_time = ?
        WHERE id = ?`,
        [now, complaintId]
      );

      // 4. 插入新的状态历史记录（重新处理操作）
      await connection.execute(
        `INSERT INTO complaint_status_history (
          complaint_id, from_status, to_status, action, 
          operator_id, operator_name, operation_time, operation_comment
        ) VALUES (?, ?, 'pending', '管理员重新处理', ?, ?, ?, ?)`,
        [
          complaintId, 
          complaint.status, 
          adminUser.id, 
          adminUser.name || adminUser.username || '管理员', 
          now, 
          `管理员${adminUser.name || adminUser.username || '管理员'}重新处理了投诉建议，状态重置为待处理`
        ]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '重新处理成功，投诉建议已重置为待处理状态',
        data: {
          complaintId: complaintId,
          newStatus: 'pending',
          reprocessTime: now.toISOString(),
          operator: adminUser.name || adminUser.username || '管理员'
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    console.error('重新处理投诉建议失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取用户总反馈建议数量接口
router.get('/user-count', async (req, res) => {
  try {
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 查询用户总反馈建议数量
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total_count 
       FROM complaints 
       WHERE user_id = ? AND deleted_at IS NULL`,
      [userId]
    );

    const totalCount = countResult[0].total_count || 0;

    res.json({
      success: true,
      message: '获取用户反馈建议数量成功',
      data: {
        userId: userId,
        totalCount: totalCount
      }
    });

  } catch (error) {
    console.error('获取用户反馈建议数量失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

// 获取投诉建议的回复记录（用户接口）
router.get('/:id/replies', async (req, res) => {
  try {
    const complaintId = req.params.id;
    
    // 从请求头或token中获取用户ID
    const userId = req.headers['user-id'] || req.query.userId;
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    // 验证用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 验证投诉建议是否存在且属于当前用户
    const [complaints] = await promisePool.execute(
      'SELECT id, status, title FROM complaints WHERE id = ? AND user_id = ? AND deleted_at IS NULL',
      [complaintId, userId]
    );

    if (complaints.length === 0) {
      return res.status(404).json({
        success: false,
        message: '投诉建议不存在'
      });
    }

    const complaint = complaints[0];

    // 查询回复记录
    const [replies] = await promisePool.execute(
      `SELECT 
        id,
        reply_content,
        reply_by,
        reply_time,
        reply_status,
        created_at
      FROM complaint_replies
      WHERE complaint_id = ?
      ORDER BY reply_time ASC`,
      [complaintId]
    );

    // 格式化返回数据
    const formattedReplies = replies.map(reply => ({
      id: reply.id,
      content: reply.reply_content,
      replyBy: reply.reply_by,
      replyTime: reply.reply_time,
      status: reply.reply_status,
      createdAt: reply.created_at,
      // 格式化时间显示
      replyTimeFormatted: new Date(reply.reply_time).toLocaleString('zh-CN')
    }));

    res.json({
      success: true,
      message: '获取回复记录成功',
      data: {
        complaintId: complaint.id,
        complaintTitle: complaint.title,
        complaintStatus: complaint.status,
        replies: formattedReplies,
        replyCount: formattedReplies.length
      }
    });

  } catch (error) {
    console.error('获取回复记录失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误，请稍后重试'
    });
  }
});

module.exports = router;
